use master
if exists (select name from sys.databases where name =N'EmployeeTranferManagement')
drop database EmployeeTranferManagement
go
create database EmployeeTranferManagement
go
use EmployeeTranferManagement
--Table Location
go
create table Location(
	STT int identity(1,1),
	LocationID nvarchar(10) not null,
	LocationName nvarchar(30) not null,
	LocationAllowance money not null
)
alter table Location
add constraint pk_LocationID primary key (LocationID)

--Table Department
go
create table Department(
	STT int identity(1,1),
	DepartmentID nvarchar(10) not null,
	DepartmentName nvarchar(30) not null,
	DepartmentAllowance money not null
)
alter table Department
add constraint pk_DepartmentID primary key (DepartmentID)

--Table Project
go
create table Project(
	STT int identity(1,1),
	ProjectID nvarchar(10) not null,
	ProjectName nvarchar(50) not null,
	ProjectAllowance money not null
)
alter table Project
add constraint pk_ProjectID primary key (ProjectID)
go

--Table Employee
go
create table Employee(
	STT int identity(1,1),
	EmployeeID nvarchar(10) not null,
	EmployeeName nvarchar(30) not null,
	Gender bit,
	DayOfBirth datetime not null,
	Phone nvarchar(15) not null,
	Address nvarchar(50) not null,
	Email nvarchar(50) not null,
	[Role] nvarchar(20) not null,
	[User] nvarchar(50) not null,
	Password nvarchar(20) not null,
	WorkExperience int not null,
	LocationID nvarchar(10) not null,
	DepartmentID nvarchar(10) not null,
	ProjectID nvarchar(10) not null
)
alter table Employee
add constraint pk_EmPloyee primary key (EmployeeID, LocationID, DepartmentID, ProjectID),
	constraint fk_Employee_Location foreign key (LocationID) references Location(LocationID),
	constraint fk_Employee_Department foreign key (DepartmentID) references Department(DepartmentID),
	constraint fk_Employee_Project foreign key (ProjectID) references Project(ProjectID)

--Table Tranfer
go
create table Transfer(
	STT int identity(1,1),
	TransferID nvarchar(10) not null,
	EmployeeID nvarchar(10) not null,
	FromProjectID nvarchar(10) not null,
	ToProjectID nvarchar(10) not null,
	FromDepartmentID nvarchar(10) not null,
	ToDepartmentID nvarchar(10) not null,
	FromLocationID nvarchar(10) not null,
	ToLocationID nvarchar(10) not null,
	JoinDate datetime null,
    EndDate datetime null,
	Status bit
)
alter table Transfer
add constraint pk_Transfer primary key (TransferID,EmployeeID),
	constraint fk_Transfer_Employee foreign key (EmployeeID,FromLocationID,FromDepartmentID,FromProjectID) references Employee(EmployeeID,LocationID,DepartmentID,ProjectID),
	constraint fk_Transfer_Project foreign key (ToProjectID) references Project(ProjectID),
	constraint fk_Transfer_Department foreign key (ToDepartmentID) references Department(DepartmentID),
	constraint fk_Transfer_Location foreign key (ToLocationID) references Location(LocationID)

--Insert Data
insert into Location values ('L01','Manager',10000)
insert into Location values ('L02','Employee',20000)

insert into Department values ('D01','Design',10000)
insert into Department values ('D02','Tester',30000)
insert into Department values ('D03','Code',50000)

insert into Project values ('P01','Employee Tranfer Management', 100000) 
insert into Project values ('P02','ATM', 110000)
insert into Project values ('P03','Football Management', 200000)
insert into Project values ('P04','', 90000)
insert into Project values ('P05','Employee Management', 180000)
insert into Project values ('P06','Website Marketing', 300000)

insert into Employee values ('E01','Nguyen Huu Tung',1,'07/07/1992','0986771743','292 Hoang Mai','Catbui.nuce@gmail.com','Admin','NguyenTung','123456',10,'L01','D01','P01')
insert into Employee values ('E02','Vu Van Binh',1,'01/01/1992','0986674054','Hung Yen','BinhThuong@gmail.com','Admin','VuBinh','719155',7,'L01','D01','P01')
insert into Employee values ('E03','Ha Van Vuong',1,'07/09/1991','01236561991','292 Hoang Mai','ThinhVuong@gmail.com','User','ThinhVuong','hienvuong',5,'L02','D01','P01')
